package teaching.controller.baseInfoManage.studentBaseInfo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import teaching.controller.baseInfoManage.common.BaseInfoCommonController;
import teaching.service.baseInfoManage.common.BaseInfoCommonService;
import teaching.service.baseInfoManage.common.ExcelUtil;
import teaching.service.baseInfoManage.common.ExcelUtil.ExcelReader;
import teaching.service.baseInfoManage.studentBaseInfo.StudentBaseInfoService;

@Controller
@RequestMapping(value="studentBaseInfo")
public class StudentBaseInfoController {
     
	@Autowired
	private StudentBaseInfoService studentBaseInfoService;
	@Autowired
    private BaseInfoCommonService baseInfoCommonService;
	
	/**
	 * 分页查询学生信息map(start,pageSize,className,studentId,studentName)
	 * @author GuoFei
	 * @param response
	 * @param request
	 * @return
	 * @throws SQLException 
	 */
	@RequestMapping(value="getStudent")
	 @ResponseBody
	public Map<String, Object> getStudent(@RequestParam(value="page",required=false) Integer  page,@RequestParam(value="pageSize",required=false) Integer  pageSize,HttpServletResponse response,HttpServletRequest request) throws SQLException{
		
		List<Map<String, Object>> list = null;
		Map<String,Object> map = new HashMap<String, Object>();
		Map<String,Object> map2 = new HashMap<String, Object>();
		int start = 0; 
		 if(page!=null&&pageSize!=null){
			 start = (page-1)*pageSize;
		 }
		  
		    String id = request.getParameter("id");
		    String upId = request.getParameter("upId");	
		    Map<String,Object> map3 = this.findId(id);
		    Iterator  i=map3.entrySet().iterator();
		    String str = "";
		    while(i.hasNext()){//只遍历一次,速度快
                Map.Entry  e=(Map.Entry)i.next();      
                str=(String) e.getKey();
		    }
		    switch (str) {
				case "collegeId":
					  map.put("collegeId", map3.get("collegeId"));
					  break;
				case "majorId":
					  map.put("majorId", map3.get("majorId"));
					  break;
				case "classId":
					  map.put("classId", map3.get("classId"));
					  map.put("enrollmentTime", upId);
					  break;
				default:
					map.put("enrollmentTime", id);
					map.put("majorId", upId);
					break;
			}
		  		
			map.put("start",start);
			map.put("pageSize",pageSize);
			map.put("className", request.getParameter("className"));
			map.put("studentId", request.getParameter("studentId"));
			map.put("studentName", request.getParameter("studentName"));
//			map.put("collegeId", request.getParameter("collegeId"));
//			map.put("majorId", request.getParameter("majorId"));
//			map.put("classId", request.getParameter("classId"));
		
			
			try {
				list = studentBaseInfoService.getStudent(map);
				int countNum = studentBaseInfoService.getStudentCountNum(map);
				map2.put("stuList", list);
				map2.put("countNum", countNum);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		 
		
		return map2;
	}
	
	

	
	/**
	 * 添加素学生信息
	 * map(studentId,studentName,studentSex,studentBirth,classId,studentJob,
	 * bankCard,IdNum,studentPhoto,phone,studentEmail)
	 * @author GuoFei
	 * @param response
	 * @param request
	 * @return
	 * @throws ParseException 
	 */
	@RequestMapping(value="addStudent")
	 @ResponseBody
	public Map<String,Object> addStudent(HttpServletResponse response,HttpServletRequest request) throws ParseException{
		
		Map<String,Object> resultMap = new HashMap<String, Object>();
		Map<String,Object> map = new HashMap<String, Object>();
		map.put("studentId", request.getParameter("studentId"));
		map.put("studentName", request.getParameter("studentName"));
		map.put("studentSex", request.getParameter("studentSex"));
		map.put("studentBirth", request.getParameter("studentBirth"));
		map.put("classId", request.getParameter("classId"));
		map.put("studentJob", request.getParameter("studentJob"));
		map.put("bankCard", request.getParameter("bankCard"));
		map.put("IdNum", request.getParameter("IdNum"));
		map.put("studentPhoto", request.getParameter("studentPhoto"));
		map.put("phone", request.getParameter("phone"));
		map.put("studentEmail", request.getParameter("studentEmail"));
		 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");		
		 map.put("createTime", sdf.parse(sdf.format(new Date())));
		List<Map<String,Object>>  list = new ArrayList<Map<String,Object>>();
		try {
			list.add(map);
			int status = studentBaseInfoService.addStudent(list);
			if(status == 1){
				resultMap.put("message", "添加学生成功");
			}else{
				resultMap.put("message", "添加学生失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultMap;
	}
	
	
	/**
	 *  删除学生信息
	 * @author GuoFei
	 * @param response
	 * @param request
	 * @return
	 */
	@RequestMapping(value="deleteStudent")
	 @ResponseBody
	public Map<String,Object> deleteStudent(HttpServletResponse response,HttpServletRequest request){
		
		Map<String,Object> map = new HashMap<String, Object>();
		String studentId = request.getParameter("studentId");
		try {
			int status = studentBaseInfoService.deleteStudent(studentId);
			if(status == 1){
				map.put("message", "删除学生成功");
			}else{
				map.put("message", "删除学生失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}
	
	/**
	 * 修改学生信息
	 * map(oldStudentId,studentId,studentName,studentSex,studentBirth,
	 * classId,studentJob,bankCard,IdNum,studentPhoto,phone,studentEmail)
	 * @author GuoFei
	 * @param response
	 * @param request
	 * @return
	 */
	@RequestMapping(value="updateStudent")
	 @ResponseBody
	public Map<String,Object> updateStudent(HttpServletResponse response,HttpServletRequest request){
		
		Map<String,Object> resultMap = new HashMap<String, Object>();
		Map<String,Object> map = new HashMap<String, Object>();
		map.put("oldStudentId", request.getParameter("oldStudentId"));
		map.put("studentId", request.getParameter("studentId"));
		map.put("studentName", request.getParameter("studentName"));
		map.put("studentSex", request.getParameter("studentSex"));
		map.put("studentBirth", request.getParameter("studentBirth"));
		map.put("classId", request.getParameter("classId"));
		map.put("studentJob", request.getParameter("studentJob"));
		map.put("bankCard", request.getParameter("bankCard"));
		map.put("IdNum", request.getParameter("IdNum"));
		map.put("studentPhoto", request.getParameter("studentPhoto"));
		map.put("phone", request.getParameter("phone"));
		map.put("studentEmail", request.getParameter("studentEmail"));
		try {
			int status = studentBaseInfoService.updateStudent(map);
			if(status == 1){
				resultMap.put("message", "修改学生信息成功");
			}else{
				resultMap.put("message", "修改学生信息失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resultMap;
	}
	
	 /**
	  * 导出学生
	  * @param response
	  * @param request
	  * @return
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 * @throws SQLException 
	  */
	 @RequestMapping(value="exportStudent")
	 @ResponseBody
	 public Map<String,Object> exportStudent(HttpServletRequest request) throws FileNotFoundException, IOException, SQLException{
		  
		    Map<String,Object> map = new HashMap<String, Object>(); 	    
			String basePath = this.getClass().getResource("/").getPath();
			if(System.getProperty("os.name").toLowerCase().indexOf("linux")>=0){
				basePath = basePath.substring(0, basePath.indexOf("/WEB-INF"));
			}else{
				basePath = basePath.substring(1, basePath.indexOf("/WEB-INF"));
			}
			
			String fileName = "template_student.xls";		
			String templatePath = basePath + "/template/baseInfoManage/" + fileName;
			// 先读取模板 
		    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(templatePath));
		    HSSFWorkbook wb = new HSSFWorkbook(fs);  
		    //在webbook中添加一个sheet,对应Excel文件中的sheet  
		    HSSFSheet sheet = wb.getSheetAt(0);  
		    //在sheet中添加表头第0行 
		    HSSFRow row;
		    HSSFCellStyle cellStyle = ExcelUtil.setCellStyle(wb);
		    //写入实体数据 ,数据从数据库得到，  
		    List< Map<String,Object>> list = null;
		    Map<String,Object> map2 = new HashMap<String, Object>();	  
			map2.put("className",request.getParameter("className"));
			map2.put("studentId",request.getParameter("studentId"));
			map2.put("studentName", request.getParameter("studentName"));
			  String id = request.getParameter("id");	   
			    Map<String,Object> map3 = this.findId(id);
			    Iterator  it=map3.entrySet().iterator();
			    String str = "";
			    while(it.hasNext()){//只遍历一次,速度快
	                Map.Entry  e=(Map.Entry)it.next();      
	                str=(String) e.getKey();
			    }
			    switch (str) {
					case "collegeId":
						  map2.put("collegeId", map3.get("collegeId"));
						  break;
					case "majorId":
						  map2.put("majorId", map3.get("majorId"));
						  break;
					case "classId":
						  map2.put("classId", map3.get("classId"));
						  break;
					default:
						map2.put("enrollmentTime", id);
						break;
				}
			//map2.put("collegeId", request.getParameter("collegeId"));
			//map2.put("majorId", request.getParameter("majorId"));
			//map2.put("enrollmentTime", request.getParameter("enrollmentTime"));
			//map2.put("classId", request.getParameter("classId"));
		    try {
				list = studentBaseInfoService.getStudent(map2);
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		    
		    if(list!=null){
		    	for (int i = 0; i < list.size(); i++) {
		    		 row = sheet.createRow( i + 1); 		    				                
		            row.createCell(0).setCellValue(list.get(i).get("studentId").toString()); 
		            row.getCell(0).setCellStyle(cellStyle);
		            row.createCell(1).setCellValue(list.get(i).get("studentName").toString()); 
		            row.getCell(1).setCellStyle(cellStyle);
		            row.createCell(2).setCellValue(list.get(i).get("studentSex").toString());
		            row.getCell(2).setCellStyle(cellStyle);
		            row.createCell(3).setCellValue(list.get(i).get("studentBirth").toString()); 
		            row.getCell(3).setCellStyle(cellStyle);
		            row.createCell(4).setCellValue(list.get(i).get("className").toString()); 
		            row.getCell(4).setCellStyle(cellStyle);
		            row.createCell(5).setCellValue(list.get(i).get("studentJob").toString()); 
		            row.getCell(5).setCellStyle(cellStyle);
		            row.createCell(6).setCellValue(list.get(i).get("phone").toString()); 
		            row.getCell(6).setCellStyle(cellStyle);
		            row.createCell(7).setCellValue(list.get(i).get("bankCard").toString()); 
		            row.getCell(7).setCellStyle(cellStyle);
		            row.createCell(8).setCellValue(list.get(i).get("IdNum").toString()); 
		            row.getCell(8).setCellStyle(cellStyle);
		            row.createCell(9).setCellValue(list.get(i).get("studentEmail").toString()); 
		            row.getCell(9).setCellStyle(cellStyle);
		        
			                  		     	     
			    }  
		    }
		  
		    
	    try{  
	    	String path = "file/export";
	    	String realPath = request.getSession().getServletContext().getRealPath("/"+path);
	    	String pathtrue = realPath + "/"+fileName;
	        FileOutputStream fout = new FileOutputStream(pathtrue);  
	        wb.write(fout);  
	        fout.close();  
	        String uri =  request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort();
	       
	        map.put("message", uri+"/"+"TeachingMIS/"+path+"/"+fileName);
	    }  
	    catch (Exception e){ 
	    
	    	map.put("message", "导出失败！");
	        e.printStackTrace();  
	    }  
		 
		 return map;
	 }
	 
	 /**
		 * 导入学生
		 * @param fileName
		 * @return
		 * @throws SQLException 
	 * @throws ParseException 
		 */
		 @RequestMapping(value="importStudent")
		 @ResponseBody
		 public Map<String,Object> importStudent(@RequestParam("file") MultipartFile file,HttpServletRequest request) throws SQLException, ParseException{
			    String fileName = file.getOriginalFilename();
				String saveFileName = UUID.randomUUID().toString().replaceAll("-", "") + "-" + fileName;
				String path = "file/import";
				String realPath = request.getSession().getServletContext().getRealPath("/"+path);
				String pathtrue = realPath + "/"+saveFileName;
				try {
					file.transferTo(new File(pathtrue));
				} catch (IllegalStateException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
			
			 Map<String,Object> map = new HashMap<String, Object>();
			 ExcelUtil excelUtil = new ExcelUtil();
			 InputStream is = null;
			
			 try {
				is = new FileInputStream(pathtrue);
			 } catch (FileNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			 }
			 ExcelReader excelReader = excelUtil.getExcelReader();
			 List<Map<Integer, String>>  list = excelReader.readExcelContent(is);	
			 List<Map<String,Object>>  list2 = new ArrayList<Map<String,Object>>();
			 if(list!=null){
				 for(int i = 0;i<list.size();i++){	
					 if(list.get(i).get(0) != null&&!"".equals(list.get(i).get(0))){
						 Map<String,Object> map2 = new HashMap<String, Object>();
						 map2.put("studentId",list.get(i).get(0));
						 map2.put("studentName", list.get(i).get(1));									
						 map2.put("studentSex", list.get(i).get(2));
						 map2.put("studentBirth", list.get(i).get(3));
						 map2.put("classId", baseInfoCommonService.getClassId(list.get(i).get(4)));
						 map2.put("studentJob", list.get(i).get(5));	
						 map2.put("phone", list.get(i).get(6));
						 map2.put("bankCard", list.get(i).get(7));	
						 map2.put("IdNum", list.get(i).get(8));	
						 map2.put("studentEmail",list.get(i).get(9));
						 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");		
						 map2.put("createTime", sdf.parse(sdf.format(new Date())));
						 map2.put("studentPhoto", null);
						
						 list2.add(map2);					
					 }		 
				 }
			 }	
			 studentBaseInfoService.addStudent(list2); 
			 File file2 = new File(pathtrue);
			 if(file2.exists()){
				 file2.delete();
			 }
		     map.put("message", "导入成功！");
			
			 return map;
		 
		 }
	 
		  /**
		     * 查询树的时候判断ID是属于哪一学院，专业，班级
		     * @param id
		     * @return
		     * @throws SQLException
		     */
		    public  Map<String,Object>  findId(String id) throws SQLException{
		    	Map<String,Object> map = new  HashMap<String, Object>();
		    	List<Map<String,Object>> xyList = baseInfoCommonService.getXy();
		    	List<Map<String,Object>> majorList = baseInfoCommonService.getAllMajorAndId();
		    	List<Map<String,Object>> classList = baseInfoCommonService.getClassNameAndId();
		    	for(Map<String,Object> obj :xyList){
		    		if(obj.get("id").equals(id)){
		    			map.put("collegeId", id);
		    			return map;
		    		}
		    	}
		    	for(Map<String,Object> obj :majorList){
		    		if(obj.get("majorID").equals(id)){
		    			map.put("majorId", id);
		    			return map;
		    		}
		    	}
		    	for(Map<String,Object> obj :classList){
		    		if(obj.get("classId").equals(id)){
		    			map.put("classId", id);
		    			return map;
		    		}
		    	}
		    	return map;
		   
		    }
			
}
